# myapp/views.py
from django.db import connection
from django.http import JsonResponse
from django.shortcuts import render

def index(request):
    return render(request, 'index.html')


#来源数据统计
def get_source_counts(request):
    with connection.cursor() as cursor:
        # 查询 source_id 及其数量
        cursor.execute("""
            SELECT source, COUNT(*) AS count
            FROM data_user_behavior
            GROUP BY source;
        """)
        columns = [col[0] for col in cursor.description]
        data = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
    return JsonResponse(data, safe=False)

#系统数据统计
def get_system_counts(request):
    with connection.cursor() as cursor:
        # 查询 system_id 及其数量
        cursor.execute("""
        SELECT system, COUNT(*) AS count
        FROM data_user_behavior 
        GROUP BY system;
        """)
        columns = [col[0] for col in cursor.description]
        data = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
    return JsonResponse(data, safe=False)


#粉丝数据统计
def get_fans_counts(request):
    with connection.cursor() as cursor:
        # 查询 fans_id 及其数量
        cursor.execute("""
        SELECT fans, COUNT(*) AS count
        FROM data_user_behavior
        GROUP BY fans;
        """)
        columns = [col[0] for col in cursor.description]
        data = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
    return JsonResponse(data, safe=False)


#完播率数据统计
def get_finash_counts(request):
    with connection.cursor() as cursor:
        # 查询 finash_id 及其数量
        cursor.execute("""
        SELECT finash, COUNT(*) AS count
        FROM data_user_behavior
        GROUP BY finash;
        """)
        columns = [col[0] for col in cursor.description]
        data = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
    return JsonResponse(data, safe=False)



#动态图   实时滚动用户id，作品id，时间，点赞，评论，收藏
def get_user_behavior(request):
    with connection.cursor() as cursor:
        # 查询 user_behavior 表
        cursor.execute("""
        SELECT user_id,`timestamp`,opus_id,thumb,collection,comment FROM data_user_behavior
        ORDER BY `timestamp` DESC
        """)
        columns = [col[0] for col in cursor.description]
        data = [
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
        return JsonResponse(data, safe=False)

